﻿if exists (select * from dbo.sysobjects where name = 'tg_Update_Level_Departments')	drop trigger [dbo].[tg_Update_Level_Departments]Go CREATE TRIGGER dbo.tg_Update_Level_Departments on [Departments]
WITH ENCRYPTION
after insert, update
as
	declare @dpID int
	declare @i_dpLevel nvarchar(255), @d_dpLevel nvarchar(255), @dpLevel nvarchar(255), @temp_treecode nvarchar(255)
	declare @dpOrd int
	Select @dpID=dpID from inserted
	Select @i_dpLevel=dpLevel from inserted
	Select @d_dpLevel=dpLevel from deleted
	if @d_dpLevel<>''
		Select @d_dpLevel=dpLevel from deleted
	else
		select @d_dpLevel='100'
	Select @dpOrd=dpOrd from inserted
	if len(@i_dpLevel)>5
		Select @dpLevel= left(dpLevel,len(dpLevel)-5) + right('000000000'+ cast(@dpOrd as varchar(5)),5) from inserted
	else
		Select @dpLevel=right('000000000'+ cast(@dpOrd as varchar(5)),5) from inserted
	update [Departments] set dpOrd=cast(right(dpLevel,5) as int)
	if exists(select dpOrd from [Departments] where len(dpLevel)=len(@i_dpLevel) and dpID<>@dpID and dpOrd in (select dpOrd from inserted))
		BEGIN
			if len(@i_dpLevel)>5
				BEGIN
					update [Departments] set dpLevel=left(dpLevel,len(@i_dpLevel)-5) + right('000000000'+ cast(cast(substring(dpLevel,(len(@i_dpLevel)-5)+1,5) as int)+1 as varchar(5)),5) + substring(dpLevel,len(@i_dpLevel)+1,100), dpOrd=dpOrd+1
					where cast(substring(dpLevel,(len(@i_dpLevel)-5)+1,5) as int)>=cast(@dpOrd as varchar(5)) and left(dpLevel,len(@i_dpLevel)-5)=left(@i_dpLevel,len(@i_dpLevel)-5)
				END
			else
				BEGIN
					update [Departments] set dpLevel=right('000000000'+ cast(cast(substring(dpLevel,(len(@i_dpLevel)-5)+1,5) as int)+1 as varchar(5)),5) + substring(dpLevel,len(@i_dpLevel)+1,100), dpOrd=dpOrd+1
					where cast(substring(dpLevel,(len(@i_dpLevel)-5)+1,5) as int)>= cast(@dpOrd as varchar(5))
				END
			select @temp_treecode=dpLevel from [Departments] where dpID= cast(@dpID as varchar(5))
			update [Departments] set dpLevel=@dpLevel where dpID= cast(@dpID as varchar(5))
			if @d_dpLevel<>'100'
				BEGIN
					update [Departments] set dpLevel=@dpLevel + substring(dpLevel,len(@temp_treecode)+1,100) where left(dpLevel,len(@temp_treecode))= @temp_treecode
				END
			update [Departments] set dpOrd=cast(right(dpLevel,5) as int)
		END
	else
		BEGIN
			update [Departments] set dpLevel=@dpLevel where dpID= cast(@dpID as varchar(5))
			if @d_dpLevel<>'100'
				BEGIN
					update [Departments] set dpLevel=@dpLevel + substring(dpLevel,len(@d_dpLevel)+1,100) where left(dpLevel,len(@d_dpLevel))= @d_dpLevel
				END
			update [Departments] set dpOrd=cast(right(dpLevel,5) as int)
		END
Go 